# models.py
import sqlite3

def safe_execute(conn: sqlite3.Connection, query: str, params: tuple = ()) -> bool:
    """安全执行SQL语句，包含错误处理"""
    try:
        cursor = conn.cursor()
        cursor.execute(query, params)
        conn.commit()
        return True
    except sqlite3.IntegrityError as e:
        print(f"完整性错误: {e}")
        conn.rollback()
        return False
    except sqlite3.OperationalError as e:
        print(f"操作错误: {e}")
        conn.rollback()
        return False
    except sqlite3.Error as e:
        print(f"数据库错误: {e}")
        conn.rollback()
        return False


def init_database(conn: sqlite3.Connection):
    """初始化数据库表结构"""
    tables = [
        '''
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE NOT NULL,
            email TEXT UNIQUE NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
        ''',
        '''
        CREATE TABLE IF NOT EXISTS posts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            content TEXT NOT NULL,
            author_id INTEGER NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (author_id) REFERENCES users (id)
        )
        '''
    ]

    for table_sql in tables:
        safe_execute(conn, table_sql)